// lib/services/database/database.dart
import 'dart:io';
import 'package:drift/drift.dart';
import 'package:drift/native.dart';
import 'package:path_provider/path_provider.dart';
import 'package:path/path.dart' as p;

part 'database.g.dart';

class StockCodes extends Table {
  TextColumn get symbol => text()();
  TextColumn get name => text().nullable()();
  DateTimeColumn get addedDate => dateTime().withDefault(currentDateAndTime)();
  DateTimeColumn get listingDate => dateTime().nullable()(); // 上市时间
  DateTimeColumn get delistingDate => dateTime().nullable()(); // 退市日期
  IntColumn get securityType => integer().withDefault(const Constant(1))(); // 证券类型
  IntColumn get listingStatus => integer().withDefault(const Constant(1))(); // 上市状态

  @override
  Set<Column> get primaryKey => {symbol};
}

class BuyRecords extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get symbol => text()();
  TextColumn get name => text()();
  RealColumn get buyPrice => real()();
  IntColumn get quantity => integer()();
  DateTimeColumn get buyDate => dateTime()();
  IntColumn get remainingQuantity => integer().withDefault(const Constant(0))();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

class SellRecords extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get buyRecordId => integer().references(BuyRecords, #id)();
  RealColumn get sellPrice => real()();
  IntColumn get sellQuantity => integer()();
  DateTimeColumn get sellDate => dateTime()();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
}

// 新增的买入条件表
class BuyConditions extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get conditionName => text()(); // 条件名称
  DateTimeColumn get listingDate => dateTime().nullable()(); // 上市时间
  RealColumn get recentChange => real().nullable()(); // 近期涨跌幅（百分比）
  RealColumn get eps => real().nullable()(); // 每股收益
  RealColumn get navps => real().nullable()(); // 每股净资产
  RealColumn get capitalReserve => real().nullable()(); // 资本公积金
  IntColumn get attentionLevel => integer().nullable()(); // 关注级别（1-5）

  @override
  Set<Column> get primaryKey => {id};
}

@DriftDatabase(tables: [StockCodes, BuyRecords, SellRecords, BuyConditions]) // 添加BuyConditions表
class AppDatabase extends _$AppDatabase {
  AppDatabase([QueryExecutor? executor]) : super(executor ?? _openConnection());

  // AppDatabase() : super(_openConnection());


  @override
  int get schemaVersion => 6; // 增加版本号（从5增加到6）

  // 确保数据库已打开
  Future<void> ensureDatabaseIsOpen() async {
    await customSelect('SELECT 1').get();
  }

// ========== StockCodes 表操作方法 ==========

  // 获取所有自选股
  Future<List<StockCode>> get allStockCodes => select(stockCodes).get();

  // 插入股票代码
  Future<int> insertStockCode(StockCodesCompanion companion) =>
      into(stockCodes).insert(companion);

  // 删除股票代码
  Future<int> deleteStockCode(String symbol) =>
      (delete(stockCodes)..where((t) => t.symbol.equals(symbol))).go();

  // 检查股票代码是否存在
  Future<bool> hasStockCode(String symbol) async {
    final query = select(stockCodes)..where((t) => t.symbol.equals(symbol));
    final result = await query.get();
    return result.isNotEmpty;
  }

  // 在AppDatabase类中添加
  Future<List<BuyRecord>> getBuyRecordsBySearch(String query) async {
    if (query.isEmpty) {
      return await allBuyRecords;
    }

    return (select(buyRecords)
      ..where((tbl) => tbl.symbol.like('%$query%') | tbl.name.like('%$query%')))
        .get();
  }


  // 辅助方法：正确创建 StockCodesCompanion
  StockCodesCompanion createStockCompanion(
      String symbol, {
        String? name,
        DateTime? listingDate,
        DateTime? delistingDate,
        int? securityType,
        int? listingStatus,
      }) {
    return StockCodesCompanion(
      symbol: Value(symbol),
      name: name != null ? Value(name) : const Value.absent(),
      listingDate: listingDate != null ? Value(listingDate) : const Value.absent(),
      delistingDate: delistingDate != null ? Value(delistingDate) : const Value.absent(),
      securityType: securityType != null ? Value(securityType) : const Value.absent(),
      listingStatus: listingStatus != null ? Value(listingStatus) : const Value.absent(),
    );
  }

  // 批量插入股票代码
  Future<void> batchInsertStockCodes(List<StockCodesCompanion> companions) async {
    await batch((batch) {
      for (final companion in companions) {
        batch.insert(stockCodes, companion);
      }
    });
  }

  // 获取股票数量
  Future<int> getStockCount() async {
    final result = await customSelect(
      'SELECT COUNT(*) as count FROM stock_codes',
      readsFrom: {stockCodes},
    ).get();

    return result.first.read<int>('count') ?? 0;
  }

  // 添加一个辅助方法来创建 StockCodesCompanion
  StockCodesCompanion createStockCodeCompanion(
      String symbol, {
        String? name,
        DateTime? listingDate,
        DateTime? delistingDate,
        int? securityType,
        int? listingStatus,
      }) {
    return StockCodesCompanion(
      symbol: Value(symbol),
      name: name != null ? Value(name) : const Value.absent(),
      listingDate: listingDate != null ? Value(listingDate) : const Value.absent(),
      delistingDate: delistingDate != null ? Value(delistingDate) : const Value.absent(),
      securityType: securityType != null ? Value(securityType) : const Value.absent(),
      listingStatus: listingStatus != null ? Value(listingStatus) : const Value.absent(),
    );
  }

  // ========== BuyRecords 表操作方法 ==========

  // 获取所有买入记录
  Future<List<BuyRecord>> get allBuyRecords => select(buyRecords).get();

  // 插入买入记录
  Future<int> insertBuyRecord(BuyRecordsCompanion companion) =>
      into(buyRecords).insert(companion);

  // 更新买入记录
  Future<bool> updateBuyRecord(BuyRecord record) =>
      update(buyRecords).replace(record);

  // 删除买入记录
  Future<int> deleteBuyRecord(int id) =>
      (delete(buyRecords)..where((t) => t.id.equals(id))).go();

  // 根据ID获取买入记录
  Future<BuyRecord?> getBuyRecordById(int id) =>
      (select(buyRecords)..where((t) => t.id.equals(id))).getSingleOrNull();

  // 根据股票代码获取买入记录
  Future<List<BuyRecord>> getBuyRecordsBySymbol(String symbol) =>
      (select(buyRecords)..where((t) => t.symbol.equals(symbol))).get();

  // 更新剩余数量
  Future<void> updateRemainingQuantity(int id, int remaining) async {
    await (update(buyRecords)
      ..where((t) => t.id.equals(id)))
        .write(BuyRecordsCompanion(remainingQuantity: Value(remaining)));
  }

  // ========== SellRecords 表操作方法 ==========

  // 获取所有卖出记录
  Future<List<SellRecord>> get allSellRecords => select(sellRecords).get();

  // 插入卖出记录
  Future<int> insertSellRecord(SellRecordsCompanion companion) =>
      into(sellRecords).insert(companion);

  // 根据买入记录ID获取卖出记录
  Future<List<SellRecord>> getSellRecordsByBuyId(int buyRecordId) =>
      (select(sellRecords)..where((t) => t.buyRecordId.equals(buyRecordId))).get();

  // 删除卖出记录
  Future<int> deleteSellRecord(int id) =>
      (delete(sellRecords)..where((t) => t.id.equals(id))).go();

  // 获取股票持仓汇总
  Future<List<Map<String, dynamic>>> getPortfolioSummary() async {
    final result = await customSelect('''
      SELECT 
        symbol,
        name,
        SUM(remainingQuantity) as total_quantity,
        AVG(buyPrice) as avg_buy_price,
        SUM(buyPrice * remainingQuantity) as total_investment
      FROM buy_records
      WHERE remainingQuantity > 0
      GROUP BY symbol, name
      ORDER BY total_investment DESC
    ''', readsFrom: {buyRecords}).get();

    return result.map((row) {
      return {
        'symbol': row.read<String>('symbol'),
        'name': row.read<String>('name'),
        'total_quantity': row.read<int>('total_quantity'),
        'avg_buy_price': row.read<double>('avg_buy_price'),
        'total_investment': row.read<double>('total_investment'),
      };
    }).toList();
  }

  // 获取股票的交易历史
  Future<List<Map<String, dynamic>>> getTradeHistory(String symbol) async {
    // 获取买入记录
    final buyRecordsList = await (select(buyRecords)
      ..where((t) => t.symbol.equals(symbol)))
        .get();

    // 获取卖出记录
    final sellRecordsList = await (select(sellRecords)
      ..where((t) => t.buyRecordId.isIn(buyRecordsList.map((r) => r.id))))
        .get();

    // 合并记录并按时间排序
    final List<Map<String, dynamic>> history = [];


    // 添加自定义SQL执行方法
    Future<void> customStatement(String sql) {
      return customUpdate(sql, updates: {stockCodes});
    }

    for (final buyRecord in buyRecordsList) {
      history.add({
        'type': 'buy',
        'id': buyRecord.id,
        'price': buyRecord.buyPrice,
        'quantity': buyRecord.quantity,
        'remaining': buyRecord.remainingQuantity,
        'date': buyRecord.buyDate,
        'createdAt': buyRecord.createdAt,
      });
    }

    for (final sellRecord in sellRecordsList) {
      history.add({
        'type': 'sell',
        'id': sellRecord.id,
        'buyRecordId': sellRecord.buyRecordId,
        'price': sellRecord.sellPrice,
        'quantity': sellRecord.sellQuantity,
        'date': sellRecord.sellDate,
        'createdAt': sellRecord.createdAt,
      });
    }

    // 按日期排序
    history.sort((a, b) => b['date'].compareTo(a['date']));

    return history;
  }

  // ========== BuyConditions 表操作方法 ==========

  // 获取所有买入条件
  Future<List<BuyCondition>> get allBuyConditions => select(buyConditions).get();

  // 插入买入条件
  Future<int> insertBuyCondition(BuyConditionsCompanion companion) =>
      into(buyConditions).insert(companion);

  // 更新买入条件
  Future<bool> updateBuyCondition(BuyCondition condition) =>
      update(buyConditions).replace(condition);

  // 删除买入条件
  Future<int> deleteBuyCondition(int id) =>
      (delete(buyConditions)..where((t) => t.id.equals(id))).go();

  // 根据ID获取买入条件
  Future<BuyCondition?> getBuyConditionById(int id) =>
      (select(buyConditions)..where((t) => t.id.equals(id))).getSingleOrNull();

  // 根据条件名称获取买入条件
  Future<List<BuyCondition>> getBuyConditionsByName(String name) =>
      (select(buyConditions)..where((t) => t.conditionName.equals(name))).get();

  // 创建买入条件的Companion辅助方法
  BuyConditionsCompanion createBuyConditionCompanion({
    required String conditionName,
    DateTime? listingDate,
    double? recentChange,
    double? eps,
    double? navps,
    double? capitalReserve,
    int? attentionLevel,
  }) {
    return BuyConditionsCompanion(
      conditionName: Value(conditionName),
      listingDate: listingDate != null ? Value(listingDate) : const Value.absent(),
      recentChange: recentChange != null ? Value(recentChange) : const Value.absent(),
      eps: eps != null ? Value(eps) : const Value.absent(),
      navps: navps != null ? Value(navps) : const Value.absent(),
      capitalReserve: capitalReserve != null ? Value(capitalReserve) : const Value.absent(),
      attentionLevel: attentionLevel != null ? Value(attentionLevel) : const Value.absent(),
    );
  }


}

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'db.sqlite'));
    return NativeDatabase(file);
  });
}